﻿/*
 * Copyright © 2025−2026 WEI.ZHOU (Willis) (Chengdu). All rights reserved.
 *
 * THIS SOURCE CODE IS PROPRIETARY AND CONFIDENTIAL.
 * It is the exclusive property of WEI.ZHOU (Willis) and may not be
 * used, copied, distributed, or disclosed outside of WEI.ZHOU (Willis)
 * without express written permission.
 *
 * This code is provided to WEI.ZHOU (Willis) under a limited, 
 * non-transferable internal license, and is for use only with the author's 
 * approved purposes. No external license is granted or implied.
 *
 * Any unauthorized use, reproduction, or distribution is strictly prohibited
 * and may result in severe civil and criminal penalties.
 */

using Aore.Core.Enums;
using Dapper;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Reflection;

// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable PossibleMultipleEnumeration
// ReSharper disable FunctionRecursiveOnAllPaths

namespace Aore.Core.Repository.Dapper.Db.Contrib;

public static class DbExtensions
{
  #region Private helper methods
  
  /// <summary>
  /// 获取表名
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  private static string GetTableName<T>() where T : class
  {
    Type type = typeof(T);
    TableAttribute? tableAttr = type.GetCustomAttribute<TableAttribute>();
    return tableAttr?.Name ?? type.Name;
  }

  /// <summary>
  /// 获取主键字段名
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  private static PropertyInfo GetKeyProperty<T>() where T : class
  {
    PropertyInfo[] properties = typeof(T).GetProperties();
    return properties.FirstOrDefault(p => p.GetCustomAttribute<KeyAttribute>() != null)
           ?? properties.FirstOrDefault(p => p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
  }

  /// <summary>
  /// 获取NotMapped外的字段名
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  private static IEnumerable<PropertyInfo> GetProperties<T>() where T : class
  {
    return typeof(T).GetProperties().Where(p => p.GetCustomAttribute<NotMappedAttribute>() == null);
  }

  /// <summary>
  /// 构建插入语句
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  private static string BuildInsertQuery<T>() where T : class
  {
    string tableName = GetTableName<T>();
    IEnumerable<PropertyInfo> properties = GetProperties<T>().Where(p => p.GetCustomAttribute<KeyAttribute>() != null);
    IEnumerable<string> columns = properties.Select(GetColumnName);
    IEnumerable<string> values = properties.Select(p => $"@{p.Name}");

    return
      $"INSERT INTO {tableName} ({string.Join(", ", columns)}) VALUES ({string.Join(", ", values)}); SELECT CAST(SCOPE_IDENTITY() as int)";
  }

  /// <summary>
  /// 构建更新语句
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  private static string BuildUpdateQuery<T>() where T : class
  {
    string tableName = GetTableName<T>();
    PropertyInfo keyProperty = GetKeyProperty<T>();
    IEnumerable<PropertyInfo> properties = GetProperties<T>().Where(p => p != keyProperty);
    IEnumerable<string> setClause = properties.Select(p => $"{GetColumnName(p)} = @{p.Name}");

    return
      $"UPDATE {tableName} SET {string.Join(", ", setClause)} WHERE {GetColumnName(keyProperty)} = @{keyProperty.Name}";
  }

  /// <summary>
  /// 构建删除语句
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  private static string BuildDeleteQuery<T>() where T : class
  {
    string tableName = GetTableName<T>();
    PropertyInfo keyProperty = GetKeyProperty<T>();
    return $"DELETE FROM {tableName} WHERE {GetColumnName(keyProperty)} = @{keyProperty.Name}";
  }

  /// <summary>
  /// 获取列名
  /// </summary>
  /// <param name="property"></param>
  /// <returns></returns>
  private static string GetColumnName(PropertyInfo property)
  {
    ColumnAttribute? columnAttr = property.GetCustomAttribute<ColumnAttribute>();
    return columnAttr?.Name ?? property.Name;
  }

  /// <summary>
  /// 参数变量前缀替换
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="parameterSql"></param>
  /// <returns></returns>
  private static string ParameterVariablePrefixReplacement(IDbConnection connection, string parameterSql)
  {
    DatabaseType databaseType = GetDatabaseType(connection);
    
    return databaseType switch
    {
      DatabaseType.MySql => parameterSql.Replace("@", "?"),
      DatabaseType.Oracle => parameterSql.Replace("@", ":"),
      _ => parameterSql
    };
  }

  #endregion

  #region Query operation
  
  /// <summary>
  /// 批量查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<IEnumerable<T>> GetAllAsync<T>(this IDbConnection connection,
    IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"SELECT * FROM {tableName}";
    return await connection.QueryAsync<T>(sql, transaction: transaction);
  }

  /// <summary>
  /// 批量查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static IEnumerable<T> GetAll<T>(this IDbConnection connection, IDbTransaction transaction = null)
    where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"SELECT * FROM {tableName}";
    return connection.Query<T>(sql, transaction: transaction);
  }

  /// <summary>
  /// 根据主键查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="id"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<T> GetAsync<T>(this IDbConnection connection, object id, IDbTransaction transaction = null)
    where T : class
  {
    string tableName = GetTableName<T>();
    PropertyInfo keyProperty = GetKeyProperty<T>();
    string sql = $"SELECT * FROM {tableName} WHERE {GetColumnName(keyProperty)} = @Id";
    sql = ParameterVariablePrefixReplacement(connection, sql);
    return await connection.QueryFirstOrDefaultAsync<T>(sql, new { Id = id }, transaction);
  }

  /// <summary>
  /// 根据主键查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="id"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static T Get<T>(this IDbConnection connection, object id, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    PropertyInfo keyProperty = GetKeyProperty<T>();
    string sql = $"SELECT * FROM {tableName} WHERE {GetColumnName(keyProperty)} = @Id";
    sql = ParameterVariablePrefixReplacement(connection, sql);
    return connection.QueryFirstOrDefault<T>(sql, new { Id = id }, transaction);
  }

  /// <summary>
  /// 条件查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<IEnumerable<T>> QueryAsync<T>(this IDbConnection connection, string whereClause,
    object parameters = null, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"SELECT * FROM {tableName}";
    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.QueryAsync<T>(sql, parameters, transaction);
  }

  /// <summary>
  /// 条件查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static IEnumerable<T> Query<T>(this IDbConnection connection, string whereClause, object parameters = null,
    IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"SELECT * FROM {tableName}";
    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Query<T>(sql, parameters, transaction);
  }

  /// <summary>
  /// 自定义字段查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="tableName"></param>
  /// <param name="fields"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <returns></returns>
  public static async Task<IEnumerable<dynamic>> QueryFieldsAsync(this IDbConnection connection, string tableName,
    string fields, string whereClause = null, object parameters = null, IDbTransaction transaction = null)
  {
    string sql = $"SELECT {fields} FROM {tableName}";
    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.QueryAsync(sql, parameters, transaction);
  }

  /// <summary>
  /// 自定义字段查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="tableName"></param>
  /// <param name="fields"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <returns></returns>
  public static IEnumerable<dynamic> QueryFields(this IDbConnection connection, string tableName, string fields,
    string whereClause = null, object parameters = null, IDbTransaction transaction = null)
  {
    string sql = $"SELECT {fields} FROM {tableName}";
    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Query(sql, parameters, transaction);
  }

  /// <summary>
  /// 分页查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="condition"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<(IEnumerable<T> Data, int TotalCount)> QueryPagedAsync<T>(this IDbConnection connection,
    DbCondition condition, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"SELECT * FROM {tableName}";
    string countSql = $"SELECT COUNT(*) FROM {tableName}";

    if (condition.WhereClause.Length > 0)
    {
      sql += $" WHERE {condition.WhereClause}";
      countSql += $" WHERE {condition.WhereClause}";
    }

    if (condition.OrderBy.Any())
    {
      sql += $" ORDER BY {string.Join(", ", condition.OrderBy)}";
    }

    if (condition.Take.HasValue)
    {
      sql += $" OFFSET {condition.Skip ?? 0} ROWS FETCH NEXT {condition.Take} ROWS ONLY";
    }

    IEnumerable<T> data = await connection.QueryAsync<T>(sql, condition.Parameters, transaction);
    int totalCount = await connection.QuerySingleAsync<int>(countSql, condition.Parameters, transaction);

    return (data, totalCount);
  }

  /// <summary>
  /// 分页查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="condition"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static (IEnumerable<T> Data, int TotalCount) QueryPaged<T>(this IDbConnection connection,
    DbCondition condition, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"SELECT * FROM {tableName}";
    string countSql = $"SELECT COUNT(*) FROM {tableName}";

    if (condition.WhereClause.Length > 0)
    {
      sql += $" WHERE {condition.WhereClause}";
      countSql += $" WHERE {condition.WhereClause}";
    }

    if (condition.OrderBy.Any())
    {
      sql += $" ORDER BY {string.Join(", ", condition.OrderBy)}";
    }

    if (condition.Take.HasValue)
    {
      sql += $" OFFSET {condition.Skip ?? 0} ROWS FETCH NEXT {condition.Take} ROWS ONLY";
    }

    IEnumerable<T> data = connection.Query<T>(sql, condition.Parameters, transaction);
    int totalCount = connection.QuerySingle<int>(countSql, condition.Parameters, transaction);

    return (data, totalCount);
  }
  
  #endregion

  #region Insert operation
  
  // 单条插入
  public static async Task<int> InsertAsync<T>(this IDbConnection connection, T entity,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildInsertQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    PropertyInfo keyProperty = GetKeyProperty<T>();

    if (keyProperty.GetCustomAttribute<KeyAttribute>() != null)
    {
      int id = await connection.QuerySingleAsync<int>(sql, entity, transaction);
      keyProperty.SetValue(entity, id);
      return id;
    }
    return await connection.ExecuteAsync(sql, entity, transaction);
  }

  public static int Insert<T>(this IDbConnection connection, T entity, IDbTransaction transaction = null)
    where T : class
  {
    string sql = BuildInsertQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    PropertyInfo keyProperty = GetKeyProperty<T>();

    if (keyProperty.GetCustomAttribute<KeyAttribute>() != null)
    {
      int id = connection.QuerySingle<int>(sql, entity, transaction);
      keyProperty.SetValue(entity, id);
      return id;
    }
    return connection.Execute(sql, entity, transaction);
  }

  // 批量插入
  public static async Task<int> InsertBatchAsync<T>(this IDbConnection connection, IEnumerable<T> entities,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildInsertQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.ExecuteAsync(sql, entities, transaction);
  }

  public static int InsertBatch<T>(this IDbConnection connection, IEnumerable<T> entities,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildInsertQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Execute(sql, entities, transaction);
  }
  
  #endregion

  #region Update operation
  
  /// <summary>
  /// 单条更新
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="entity"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<int> UpdateAsync<T>(this IDbConnection connection, T entity,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildUpdateQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.ExecuteAsync(sql, entity, transaction);
  }

  /// <summary>
  /// 单条更新
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="entity"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static int Update<T>(this IDbConnection connection, T entity, IDbTransaction transaction = null)
    where T : class
  {
    string sql = BuildUpdateQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Execute(sql, entity, transaction);
  }

  /// <summary>
  /// 条件更新
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="updateValues"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<int> UpdateAsync<T>(this IDbConnection connection, object updateValues, string whereClause,
    object? parameters = null, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string setClause = string.Join(", ", updateValues.GetType().GetProperties().Select(p => $"{p.Name} = @{p.Name}"));
    string sql = $"UPDATE {tableName} SET {setClause}";

    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    DynamicParameters param = new(updateValues);
    if (parameters != null)
    {
      Dictionary<string, object> paramDict =
        parameters.GetType().GetProperties().ToDictionary(p => p.Name, p => p.GetValue(parameters));
      foreach (KeyValuePair<string, object> kvp in paramDict)
      {
        param.Add(kvp.Key, kvp.Value);
      }
    }

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.ExecuteAsync(sql, param, transaction);
  }

  /// <summary>
  /// 条件更新
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="updateValues"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static int Update<T>(this IDbConnection connection, object updateValues, string whereClause,
    object? parameters = null, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string setClause = string.Join(", ", updateValues.GetType().GetProperties().Select(p => $"{p.Name} = @{p.Name}"));
    string sql = $"UPDATE {tableName} SET {setClause}";

    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    DynamicParameters param = new(updateValues);
    if (parameters != null)
    {
      Dictionary<string, object> paramDict =
        parameters.GetType().GetProperties().ToDictionary(p => p.Name, p => p.GetValue(parameters));
      foreach (KeyValuePair<string, object> kvp in paramDict)
      {
        param.Add(kvp.Key, kvp.Value);
      }
    }

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Execute(sql, param, transaction);
  }

  /// <summary>
  /// 批量更新
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="entities"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<int> UpdateBatchAsync<T>(this IDbConnection connection, IEnumerable<T> entities,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildUpdateQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.ExecuteAsync(sql, entities, transaction);
  }

  public static int UpdateBatch<T>(this IDbConnection connection, IEnumerable<T> entities,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildUpdateQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Execute(sql, entities, transaction);
  }
  
  #endregion

  #region Delete operation
  
  /// <summary>
  /// 根据主键删除
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="id"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<int> DeleteAsync<T>(this IDbConnection connection, object id,
    IDbTransaction transaction = null) where T : class
  {
    string sql = BuildDeleteQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    PropertyInfo keyProperty = GetKeyProperty<T>();
    DynamicParameters parameters = new();
    parameters.Add(keyProperty.Name, id);
    return await connection.ExecuteAsync(sql, parameters, transaction);
  }

  /// <summary>
  /// 根据主键删除
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="id"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static int Delete<T>(this IDbConnection connection, object id, IDbTransaction transaction = null)
    where T : class
  {
    string sql = BuildDeleteQuery<T>();
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    PropertyInfo keyProperty = GetKeyProperty<T>();
    DynamicParameters parameters = new();
    parameters.Add(keyProperty.Name, id);
    return connection.Execute(sql, parameters, transaction);
  }

  /// <summary>
  /// 条件删除
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<int> DeleteAsync<T>(this IDbConnection connection, string whereClause,
    object parameters = null, IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"DELETE FROM {tableName}";
    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.ExecuteAsync(sql, parameters, transaction);
  }

  /// <summary>
  /// 条件删除
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="whereClause"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static int Delete<T>(this IDbConnection connection, string whereClause, object parameters = null,
    IDbTransaction transaction = null) where T : class
  {
    string tableName = GetTableName<T>();
    string sql = $"DELETE FROM {tableName}";
    if (!string.IsNullOrEmpty(whereClause))
      sql += $" WHERE {whereClause}";

    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Execute(sql, parameters, transaction);
  }

  /// <summary>
  /// 批量删除
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="ids"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<int> DeleteBatchAsync<T>(this IDbConnection connection, IEnumerable<object> ids,
    IDbTransaction transaction = null) where T : class
  {
    if (!ids.Any()) return 0;

    string tableName = GetTableName<T>();
    PropertyInfo keyProperty = GetKeyProperty<T>();
    string sql = $"DELETE FROM {tableName} WHERE {GetColumnName(keyProperty)} IN @Ids";
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return await connection.ExecuteAsync(sql, new { Ids = ids }, transaction);
  }

  /// <summary>
  /// 批量删除
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="ids"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static int DeleteBatch<T>(this IDbConnection connection, IEnumerable<object> ids,
    IDbTransaction transaction = null) where T : class
  {
    if (!ids.Any()) return 0;

    string tableName = GetTableName<T>();
    PropertyInfo keyProperty = GetKeyProperty<T>();
    string sql = $"DELETE FROM {tableName} WHERE {GetColumnName(keyProperty)} IN @Ids";
    
    sql = ParameterVariablePrefixReplacement(connection, sql);
    
    return connection.Execute(sql, new { Ids = ids }, transaction);
  }
  
  #endregion

  #region Transaction execution
  
  /// <summary>
  /// 事务执行多个操作
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="operation"></param>
  /// <returns></returns>
  public static async Task<bool> ExecuteInTransactionAsync(this IDbConnection connection,
    Func<IDbTransaction, Task<bool>> operation)
  {
    using IDbTransaction transaction = connection.BeginTransaction();
    try
    {
      bool result = await operation(transaction);
      if (result)
      {
        transaction.Commit();
        return true;
      }
      transaction.Rollback();
      return false;
    }
    catch
    {
      transaction.Rollback();
      throw;
    }
  }

  /// <summary>
  /// 事务执行多个操作
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="operation"></param>
  /// <returns></returns>
  public static bool ExecuteInTransaction(this IDbConnection connection, Func<IDbTransaction, bool> operation)
  {
    using IDbTransaction transaction = connection.BeginTransaction();
    try
    {
      bool result = operation(transaction);
      if (result)
      {
        transaction.Commit();
        return true;
      }
      transaction.Rollback();
      return false;
    }
    catch
    {
      transaction.Rollback();
      throw;
    }
  }
  
  #endregion

  #region Stored procedure execution
  
  /// <summary>
  /// 执行存储过程（支持输入输出参数）
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="procedureName"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<T> ExecuteStoredProcedureAsync<T>(this IDbConnection connection, string procedureName,
    object parameters = null, IDbTransaction transaction = null)
  {
    return await connection.QueryFirstOrDefaultAsync<T>(
      procedureName,
      parameters,
      transaction,
      commandType: CommandType.StoredProcedure);
  }

  /// <summary>
  /// 执行存储过程（支持输入输出参数）
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="procedureName"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static T ExecuteStoredProcedure<T>(this IDbConnection connection, string procedureName,
    object parameters = null, IDbTransaction transaction = null)
  {
    return connection.QueryFirstOrDefault<T>(
      procedureName,
      parameters,
      transaction,
      commandType: CommandType.StoredProcedure);
  }

  /// <summary>
  /// 执行无返回值的存储过程
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="procedureName"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <returns></returns>
  public static async Task<int> ExecuteStoredProcedureAsync(this IDbConnection connection, string procedureName,
    object parameters = null, IDbTransaction transaction = null)
  {
    return await connection.ExecuteAsync(
      procedureName,
      parameters,
      transaction,
      commandType: CommandType.StoredProcedure);
  }

  /// <summary>
  /// 执行无返回值的存储过程
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="procedureName"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <returns></returns>
  public static int ExecuteStoredProcedure(this IDbConnection connection, string procedureName,
    object parameters = null, IDbTransaction transaction = null)
  {
    return connection.Execute(
      procedureName,
      parameters,
      transaction,
      commandType: CommandType.StoredProcedure);
  }
  
  #endregion

  #region Custom SQL Execution
  
  /// <summary>
  /// 执行自定义查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="sql"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static async Task<IEnumerable<T>> QueryCustomAsync<T>(this IDbConnection connection, string sql,
    object parameters = null, IDbTransaction transaction = null)
  {
    return await connection.QueryAsync<T>(sql, parameters, transaction);
  }

  /// <summary>
  /// 执行自定义查询
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="sql"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <typeparam name="T"></typeparam>
  /// <returns></returns>
  public static IEnumerable<T> QueryCustom<T>(this IDbConnection connection, string sql, object parameters = null,
    IDbTransaction transaction = null)
  {
    return connection.Query<T>(sql, parameters, transaction);
  }

  /// <summary>
  /// 执行自定义非查询SQL
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="sql"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <returns></returns>
  public static async Task<int> ExecuteCustomAsync(this IDbConnection connection, string sql, object parameters = null,
    IDbTransaction transaction = null)
  {
    return await connection.ExecuteAsync(sql, parameters, transaction);
  }

  /// <summary>
  /// 执行自定义非查询SQL
  /// </summary>
  /// <param name="connection"></param>
  /// <param name="sql"></param>
  /// <param name="parameters"></param>
  /// <param name="transaction"></param>
  /// <returns></returns>
  public static int ExecuteCustom(this IDbConnection connection, string sql, object parameters = null,
    IDbTransaction transaction = null)
  {
    return connection.Execute(sql, parameters, transaction);
  }
  
  #endregion

  #region Other function
  
  /// <summary>
  /// 获取数据库类型
  /// </summary>
  /// <param name="connection"></param>
  /// <returns></returns>
  /// <exception cref="ArgumentNullException"></exception>
  public static DatabaseType GetDatabaseType(IDbConnection? connection)
  {
    if (connection == null)
    {
      throw new ArgumentNullException(nameof(connection), "Unknown (null connection)");
    }

    // 使用 GetType() 获取运行时类型
    Type connectionType = connection.GetType();

    // 检查具体类型
    if (connectionType.Name.Contains("SqlConnection", StringComparison.OrdinalIgnoreCase))
    {
      return DatabaseType.SqlServer;
    }
    else if (connectionType.Name.Contains("MySqlConnection", StringComparison.OrdinalIgnoreCase))
    {
      return DatabaseType.MySql;
    }
    else if (connectionType.Name.Contains("NpgsqlConnection", StringComparison.OrdinalIgnoreCase))
    {
      return DatabaseType.PostgreSQL;
    }
    else if (connectionType.Name.Contains("OracleConnection", StringComparison.OrdinalIgnoreCase))
    {
      return DatabaseType.Oracle;
    }
    else if (connectionType.Name.Contains("SqliteConnection", StringComparison.OrdinalIgnoreCase))
    {
      return DatabaseType.Sqlite;
    }
    // 可以继续添加其他数据库类型的检查
    else
    {
      throw new ArgumentNullException(nameof(connection), "Unknown (null connection)");
    }
  }
  
  #endregion
}